package org.auf.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Collection;
import java.util.LinkedList;

import org.apache.log4j.Logger;

public class PairedEventDAOJDBC implements IPairedEventDAO {
	
	static Logger log = Logger.getLogger(UserDAOJDBC.class.getName());

    private static PairedEventDAOJDBC INSTANCE = null;
    
    private PairedEventDAOJDBC() {}
    
    private synchronized static void createInstance() {
        if (INSTANCE == null) { 
            INSTANCE = new PairedEventDAOJDBC();
        }
    }
 
    public static PairedEventDAOJDBC getInstance() {
        if (INSTANCE == null) createInstance();
        return INSTANCE;
    }
	

	@Override
	public boolean deletePairedEvent() {
		int updates=0;
		ConnectionManager cm = ConnectionManager.getInstance();
		Connection db = cm.checkOut();
		
		try{
			PreparedStatement st = db.prepareStatement("DELETE FROM tblpairedevent");
			updates =st.executeUpdate();
		
		}catch(Exception e){updates=-1;log.error("Error al generar la sentencia SQL y ejecutarla: "+e);}

		cm.checkIn(db);
		// TODO Auto-generated method stub
		return updates>0;
	}

	@Override
	public boolean insertPairedEvent(User user) {
		int updates=0;
		ConnectionManager cm = ConnectionManager.getInstance();
		Connection db = cm.checkOut();
		
		try{
			PreparedStatement st = db.prepareStatement("INSERT INTO tblpairedevent (user) values (?)");
			st.setString(1, user.getId());
			updates =st.executeUpdate();
		
		}catch(Exception e){updates=-1;log.error("Error al generar la sentencia SQL y ejecutarla: "+e);}

		cm.checkIn(db);
		// TODO Auto-generated method stub
		return updates==1;
		
	}

	@Override
	public boolean selectPairedEventByUser(User user) {
		ConnectionManager cm = ConnectionManager.getInstance();
		Connection db = cm.checkOut();
		int count=0;
		
		try{
			PreparedStatement st = db.prepareStatement("SELECT * FROM tblpairedevent WHERE user=?");
			st.setString(1, user.getId());
			ResultSet rs = st.executeQuery();
			if (rs.next()){
				count++;
			}
		
		}catch(Exception e){user=null;log.error("Error al generar la sentencia SQL y ejecutarla: "+e);}

		cm.checkIn(db);
		// TODO Auto-generated method stub
		return count>0;
	}

	@Override
	public Collection<User> selectPairedEventGeeks() {
		Collection<User> sol = new LinkedList<User>();
		
		ConnectionManager cm = ConnectionManager.getInstance();
		Connection db = cm.checkOut();
		
		try{
			PreparedStatement st = db.prepareStatement("SELECT * FROM tblpairedevent, tbluser WHERE tblpairedevent.user = tbluser.id AND tbluser.geek = ? ORDER BY score DESC");
			st.setBoolean(1, true);
			ResultSet rs = st.executeQuery();
			while (rs.next()){
				User user=new User();
				user.setId( rs.getString("id"));
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setMail(rs.getString("mail"));
				user.setGeek(rs.getBoolean("geeks"));
				user.setScore(rs.getInt("score"));
				sol.add(user);
			}

		}catch(Exception e){sol=null;log.error("Error al generar la sentencia SQL y ejecutarla: "+e);}

		cm.checkIn(db);
		// TODO Auto-generated method stub
		return sol;
	}

	@Override
	public Collection<User> selectPairedEventVolunteers() {
		Collection<User> sol = new LinkedList<User>();
		
		ConnectionManager cm = ConnectionManager.getInstance();
		Connection db = cm.checkOut();
		
		try{
			PreparedStatement st = db.prepareStatement("SELECT * FROM tblpairedevent, tbluser WHERE tblpairedevent.user = tbluser.id AND tbluser.geek = ? ORDER BY score DESC");
			st.setBoolean(1, false);
			ResultSet rs = st.executeQuery();
			while (rs.next()){
				User user=new User();
				user.setId( rs.getString("id"));
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setMail(rs.getString("mail"));
				user.setGeek(rs.getBoolean("geeks"));
				user.setScore(rs.getInt("score"));
				sol.add(user);
			}

		}catch(Exception e){sol=null;log.error("Error al generar la sentencia SQL y ejecutarla: "+e);}

		cm.checkIn(db);
		// TODO Auto-generated method stub
		return sol;
	}

}
